﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBackupInventoryFiles]
#-- Purpose:		Inventories backup files that are not currently known by 
#--					msdb
#--	Last Update:	05/31/2012
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBackupInventoryFiles]
(
	@database_name		sysname
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@default_bkp_dir nvarchar(4000), @sError nvarchar(4000)
DECLARE		@progname sysname

--- Define the name of this program (for logs)
SELECT		@progname = 'spBackupInventoryFiles'

--- Get the default backup directory
EXEC		dbo.spGetBackupDirectory	@database_name = @database_name, 
										@backup_directory_name = @default_bkp_dir OUTPUT

--- Get the list of files to inventory into msdb
DECLARE crs CURSOR FOR
	SELECT		t1.physical_device_name
	FROM		dbo.GetFiles(@default_bkp_dir, 1) t1
	LEFT JOIN	msdb.dbo.backupmediafamily bmf ON t1.physical_device_name = bmf.physical_device_name
	WHERE		bmf.physical_device_name IS NULL

OPEN crs
FETCH NEXT FROM crs INTO @default_bkp_dir
WHILE @@FETCH_STATUS <> -1
  BEGIN
	BEGIN TRY
		--- Set the message
		SELECT	@sError = 'Adding backup file to msdb inventory: ' + @default_bkp_dir
		EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sError

		--- Inventory the file
		EXEC spRestoreBackupFile	@database_name = @database_name, 
									@file_name = @default_bkp_dir,
									@recover_now = 0,
									@replace_existing = 0,
									@verify_only = 1,
									@stopat_time = NULL
	END TRY
	BEGIN CATCH
		SELECT	@sError = 'Could not add file to msdb inventory: ' + @default_bkp_dir
		EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sError
	END CATCH

	FETCH NEXT FROM crs INTO @default_bkp_dir
  END
CLOSE crs
DEALLOCATE crs

SET NOCOUNT OFF